package cn.vconinfo.lims.delivery.mapper;

import cn.vconinfo.lims.delivery.entity.ImageDTO;
import cn.vconinfo.lims.delivery.entity.ProjectDocumentDTO;
import org.apache.ibatis.annotations.*;

import java.util.Date;
import java.util.List;

/**
 * @Description:
 * @Author: WHB
 * @Data: 2019-09-02 17:41
 */
public interface InspectAppMapper {

    @Select("<script>" +
            "SELECT COUNT(*) FROM specimen_application sa\n" +
            "LEFT JOIN basic_info bi ON bi.object_id = sa.patient_office_id\n" +
            "WHERE sa.status = 1\n" +
            "<if test='officeId!=null'> AND bi.office_id = #{officeId}\n</if>" +
            "<if test='startDateSearchSql!=null'> AND application_time &gt;= #{startDateSearchSql}\n</if>" +
            "<if test='endDateSearchSql!=null'> AND application_time &lt;= #{endDateSearchSql}\n</if>" +
            "<if test='officeSearch!=null'> AND patient_office_id = #{officeSearch}\n</if>" +
            "<if test='codeSearch!=null'> AND placeholder_code LIKE concat('%', #{codeSearch}, '%')\n</if>" +
            "<if test='nameSearch!=null'> AND patient_name LIKE concat('%', #{nameSearch}, '%')\n</if>" +
            "</script>")
    Long getApplicationCount(@Param("startDateSearchSql") Date startDateSearchSql, @Param("endDateSearchSql") Date endDateSearchSql,
                             @Param("officeSearch") Long officeSearch, @Param("codeSearch") String codeSearch,
                             @Param("nameSearch") String nameSearch, @Param("officeId") Long key);

    @Select("<script>" +
            "SELECT count(*) FROM specimen_request sr\n" +
//            "JOIN basic_info bi ON bi.object_id = sr.hospital_id\n" +
//            "JOIN specimen_application_link sal ON sal.request_id = sr.request_id\n" +
//            "JOIN sys_project_document spd ON sal.project_id = spd.project_document_id\n" +
//            "LEFT JOIN specimen_request_link srl ON srl.request_id = sr.request_id\n" +
            "WHERE sr.status = 1 " +
//            "AND sal.status = 1 AND sal.is_register = 0 " +
            "AND mannal =1 " +
//            "AND (sr.hospital_id = #{officeId} OR (SELECT office_id FROM basic_info where object_id= sr.hospital_id)= #{officeId}) \n" +
            "<if test='officeId!=null'> AND (SELECT office_id FROM basic_info where object_id= sr.hospital_id)= #{officeId}\n</if>" +
            "<if test='officeSearch!=null'> AND sr.hospital_id = #{officeSearch} )\n</if>" +
            "<if test='codeSearch!=null'> AND placeholder_code LIKE concat('%', #{codeSearch}, '%')\n</if>" +
            "<if test='nameSearch!=null'> AND patient_name LIKE concat('%', #{nameSearch}, '%')\n</if>" +
            "</script>")
    Long getRequestCount(@Param("officeSearch") Long officeSearch, @Param("codeSearch") String codeSearch,
                         @Param("nameSearch") String nameSearch, @Param("officeId") Long key);

//    @Select("<script>" +
//            "SELECT *,bu1.user_nickName AS enteringManName,bu2.user_nickName AS confirmManName\n" +
//            "FROM specimen_application sa\n" +
//            "LEFT JOIN basic_user bu1 ON bu1.user_id = sa.entering_man_id\n" +
//            "LEFT JOIN basic_user bu2 ON bu2.user_id = sa.confirm_man_id\n" +
//            "LEFT JOIN basic_info bi ON bi.object_id = sa.patient_office_id\n" +
//            "WHERE sa.status = 1\n" +
//            "<if test='officeId!=null'> AND bi.office_id = #{officeId}\n</if>" +
//            "<if test='startDateSearchSql!=null'> AND application_time &gt;= #{startDateSearchSql}\n</if>" +
//            "<if test='endDateSearchSql!=null'> AND application_time &lt;= #{endDateSearchSql}\n</if>" +
//            "<if test='officeSearch!=null'> AND patient_office_id = #{officeSearch}\n</if>" +
//            "<if test='codeSearch!=null'> AND placeholder_code LIKE concat('%', #{codeSearch}, '%')\n</if>" +
//            "<if test='nameSearch!=null'> AND patient_name LIKE concat('%', #{nameSearch}, '%')\n</if>" +
//            "ORDER BY application_id DESC\n" +
//            "LIMIT #{current},#{pageSize}\n" +
//            "</script>")
//    List<InspectAppDTO> getApplication(@Param("current") Integer current, @Param("pageSize") Integer pageSize,
//                                       @Param("startDateSearchSql") Date startDateSearchSql, @Param("endDateSearchSql") Date endDateSearchSql,
//                                       @Param("officeSearch") Long officeSearch, @Param("codeSearch") String codeSearch,
//                                       @Param("nameSearch") String nameSearch, @Param("officeId") Long key);
//    @Select("<script>" +
//            "SELECT *, sr.sampling_time samplingDate," +
//            "(select doctor_name  from hospital_doctor  where doctor_id = sr.application_doctor_id) applicationDoctorName," +
//            "(select type_info_name from basic_type_info where sr.application_department_id = type_info_id and type_info_type=3) applicationDepartmentName " +
//            "FROM specimen_request sr\n" +
////            "SELECT *, sr.request_id as applicationId, sr.hospital_id AS patientOfficeId, outpatient_id AS doorNum,\n" +
////            "application_department_id AS departmentId, application_doctor_id AS doctorId, check_man_id AS confirmManId,\n" +
////            "check_time AS receiveTime,  " +
////            "(select doctor_name  from hospital_doctor  where doctor_id = sr.application_doctor_id) doctorName," +
////            "(select type_info_name from basic_type_info where sr.application_department_id = type_info_id and type_info_type=3) departmentName " +
////            "FROM specimen_request sr\n" +
////            "JOIN basic_info bi ON bi.object_id = sr.hospital_id\n" +
////            "JOIN specimen_application_link sal ON sal.request_id = sr.request_id\n" +
////            "JOIN sys_project_document spd ON sal.project_id = spd.project_document_id\n" +
////            "LEFT JOIN specimen_request_link srl ON srl.request_id = sr.request_id\n" +
//            "WHERE sr.status = 1 AND mannal =1 " +
////            "\"AND (sr.hospital_id = #{officeId} OR (SELECT office_id FROM basic_info where object_id= sr.hospital_id)= #{officeId}) \n" +
//            "<if test='officeId!=null'> AND (SELECT office_id FROM basic_info where object_id= sr.hospital_id)= #{officeId}\n</if>" +
//            "<if test='officeSearch!=null'> AND sr.hospital_id = #{officeSearch}  </if>" +
//            "<if test='codeSearch!=null'> AND placeholder_code LIKE concat('%', #{codeSearch}, '%') \n</if>" +
//            "<if test='nameSearch!=null'> AND patient_name LIKE concat('%', #{nameSearch}, '%') \n</if>" +
//            "ORDER BY request_id DESC\n" +
//            "LIMIT #{current},#{pageSize}\n" +
//            "</script>")
//    List<SpecimenCheckDTO> getRequest(@Param("current") Integer current, @Param("pageSize") Integer pageSize,
//                                      @Param("officeSearch") Long officeSearch, @Param("codeSearch") String codeSearch,
//                                      @Param("nameSearch") String nameSearch, @Param("officeId") Long key);

//    @Select("<script>" +
//            "SELECT *, sr.sampling_time samplingDate," +
//            "(select doctor_name  from hospital_doctor  where doctor_id = sr.application_doctor_id) applicationDoctorName," +
//            "(select type_info_name from basic_type_info where sr.application_department_id = type_info_id and type_info_type=3) applicationDepartmentName " +
//            "FROM specimen_request sr\n" +
////            "SELECT *, sr.request_id as applicationId, sr.hospital_id AS patientOfficeId, outpatient_id AS doorNum,\n" +
////            "application_department_id AS departmentId, application_doctor_id AS doctorId, check_man_id AS confirmManId,\n" +
////            "check_time AS receiveTime,  " +
////            "(select doctor_name  from hospital_doctor  where doctor_id = sr.application_doctor_id) doctorName," +
////            "(select type_info_name from basic_type_info where sr.application_department_id = type_info_id and type_info_type=3) departmentName " +
////            "FROM specimen_request sr\n" +
////            "JOIN basic_info bi ON bi.object_id = sr.hospital_id\n" +
////            "JOIN specimen_application_link sal ON sal.request_id = sr.request_id\n" +
////            "JOIN sys_project_document spd ON sal.project_id = spd.project_document_id\n" +
////            "LEFT JOIN specimen_request_link srl ON srl.request_id = sr.request_id\n" +
//            "WHERE sr.status = 1 AND mannal =1 " +
////            "\"AND (sr.hospital_id = #{officeId} OR (SELECT office_id FROM basic_info where object_id= sr.hospital_id)= #{officeId}) \n" +
//            "<if test='officeId!=null'> AND (SELECT office_id FROM basic_info where object_id= sr.hospital_id)= #{officeId}\n</if>" +
//            "<if test='officeSearch!=null'> AND sr.hospital_id = #{officeSearch}  </if>" +
//            "<if test='codeSearch!=null'> AND placeholder_code LIKE concat('%', #{codeSearch}, '%') \n</if>" +
//            "<if test='nameSearch!=null'> AND patient_name LIKE concat('%', #{nameSearch}, '%') \n</if>" +
//            "ORDER BY request_id DESC\n" +
//            "LIMIT #{current},#{pageSize}\n" +
//            "</script>")
//    List<SpecimenCheckAndDocumentDTO> getRequest1(@Param("current") Integer current, @Param("pageSize") Integer pageSize,
//                                                  @Param("officeSearch") Long officeSearch, @Param("codeSearch") String codeSearch,
//                                                  @Param("nameSearch") String nameSearch, @Param("officeId") Long key);


    @Insert("<script>" +
            "INSERT INTO specimen_application (placeholder_code, door_num, patient_name, patient_sex, " +
            "patient_age, " +
            "patient_age_type,\n" +
            "patient_type, patient_tag, department_id, bed_num, doctor_id, application_time, patient_office_id, sampling_time,\n" +
            "entering_man_id, entering_time, confirm_man_id, confirm_time, remarks, diagnosis, status)\n" +
            "VALUES (#{placeholderCode}, #{doorNum}, #{patientName}, #{patientSex}," +
            " #{patientAge}, " +
            "#{patientAgeType},\n" +
            "#{patientType}, #{patientTag}, #{departmentId}, #{bedNum}, #{doctorId}, #{applicationTime}, #{patientOfficeId},\n" +
            "#{samplingTime}, #{enteringManId}, #{enteringTime}, #{confirmManId}, #{confirmTime}, #{remarks},\n" +
            "#{diagnosis}, 1)"+
            "</script>")
    Long addApplication(@Param("placeholderCode") String placeholderCode, @Param("doorNum") String doorNum,
                        @Param("patientName") String patientName, @Param("patientSex") Integer patientSex, @Param("patientAge") Integer patientAge, @Param("patientAgeType") Integer patientAgeType,
                        @Param("patientType") Integer patientType, @Param("patientTag") Integer patientTag, @Param("departmentId") Integer departmentId, @Param("bedNum") String bedNum,
                        @Param("doctorId") Integer doctorId, @Param("applicationTime") Date applicationTime, @Param("patientOfficeId") Long patientOfficeId,
                        @Param("samplingTime") Date samplingTime, @Param("enteringManId") Long enteringManId, @Param("enteringTime") Date enteringTime,
                        @Param("confirmManId") Long confirmManId, @Param("confirmTime") Date confirmTime, @Param("remarks") String remarks, @Param("diagnosis") String diagnosis);

    @Update("UPDATE specimen_application\n" +
            "SET placeholder_code = #{placeholderCode}, door_num = #{doorNum}, patient_name = #{patientName}, patient_sex = #{patientSex},\n" +
            "patient_age = #{patientAge}, patient_age_type = #{patientAgeType}, patient_type = #{patientType}, patient_tag = #{patientTag},\n" +
            "department_id = #{departmentId}, bed_num = #{bedNum}, doctor_id = #{doctorId}, application_time = #{applicationTime},\n" +
            "patient_office_id = #{patientOfficeId}, sampling_time = #{samplingTime},entering_man_id = #{enteringManId},\n" +
            "entering_time = #{enteringTime}, confirm_man_id = #{confirmManId}, confirm_time = #{confirmTime}, remarks = #{remarks},\n" +
            "diagnosis = #{diagnosis} WHERE application_id = #{applicationId}")
    Long editApplication(@Param("placeholderCode") String placeholderCode, @Param("doorNum") String doorNum,
                         @Param("patientName") String patientName, @Param("patientSex") Integer patientSex, @Param("patientAge") Integer patientAge, @Param("patientAgeType") Integer patientAgeType,
                         @Param("patientType") Integer patientType, @Param("patientTag") Integer patientTag, @Param("departmentId") Integer departmentId, @Param("bedNum") String bedNum,
                         @Param("doctorId") Integer doctorId, @Param("applicationTime") Date applicationTime, @Param("patientOfficeId") Long patientOfficeId,
                         @Param("samplingTime") Date samplingTime, @Param("enteringManId") Long enteringManId, @Param("enteringTime") Date enteringTime,
                         @Param("confirmManId") Long confirmManId, @Param("confirmTime") Date confirmTime, @Param("remarks") String remarks,
                         @Param("diagnosis") String diagnosis, @Param("applicationId") Long applicationId);

    @Update("UPDATE specimen_application SET status = 0 WHERE application_id = #{applicationId}")
    Long removeApplication(@Param("applicationId") Long applicationId);

    @Select("SELECT COUNT(*) FROM sys_project_document syd, specimen_application_link sal\n" +
            "WHERE syd.project_document_id = sal.project_id AND sal.application_id = #{applicationId} AND sal.status = 1")
    Long getSpecimenCount(@Param("applicationId") Long applicationId);

    @Select("SELECT COUNT(*) FROM sys_project_document syd, specimen_application_link sal\n" +
            "WHERE syd.project_document_id = sal.project_id AND sal.request_id = #{requestId} AND sal.status = 1")
    Long getRequestSpecimenCount(@Param("requestId") Long requestId);

    @Select("SELECT * FROM sys_project_document syd, specimen_application_link sal\n" +
            "WHERE syd.project_document_id = sal.project_id AND sal.application_id = #{applicationId} AND sal.status = 1\n" +
            "LIMIT #{current},#{pageSize}")
    List<ProjectDocumentDTO> getSpecimen(@Param("current") Integer current, @Param("pageSize") Integer pageSize, @Param("applicationId") Long applicationId);

    @Select("SELECT * FROM sys_project_document syd, specimen_application_link sal\n" +
            "WHERE syd.project_document_id = sal.project_id AND sal.request_id = #{requestId} AND sal.status = 1\n" +
            "LIMIT #{current},#{pageSize}")
    List<ProjectDocumentDTO> getRequestSpecimen(@Param("current") Integer current, @Param("pageSize") Integer pageSize, @Param("requestId") Long requestId);

    /**
     * 检验单据列表 不分页
     * @param requestId
     * @return
     */
    @Select("SELECT * FROM sys_project_document syd, specimen_application_link sal\n" +
            "WHERE syd.project_document_id = sal.project_id AND sal.request_id = #{requestId} AND sal.status = 1\n" )
    List<ProjectDocumentDTO> getAllRequestSpecimen(@Param("requestId") Long requestId);

    @Select("SELECT * FROM sys_project_document syd, specimen_application_link sal\n" +
            "WHERE syd.project_document_id = sal.project_id AND sal.application_id = #{applicationId} AND sal.status = 1")
    List<ProjectDocumentDTO> getAllSpecimen(@Param("applicationId") Long applicationId);

    @Select("SELECT * FROM sys_project_document syd, specimen_application_link sal\n" +
            "WHERE syd.project_document_id = sal.project_id AND sal.request_id = #{requestid} AND sal.status = 1")
    List<ProjectDocumentDTO> getAllRequsetSpecimen(@Param("requestid") Long requestId);

    @Insert("INSERT INTO specimen_application_link (project_id, application_id, status)\n" +
            "VALUES (#{projectDocumentId}, #{applicationId}, 1)")
    Long addSpecimen(@Param("projectDocumentId") Long projectDocumentId, @Param("applicationId") Long applicationId);

    @Insert("INSERT INTO specimen_application_link (project_id, request_id, status ,application_id)\n" +
            "VALUES (#{projectDocumentId}, #{requestid}, 1,-1)")
    Long addRequestSpecimen(@Param("projectDocumentId") Long projectDocumentId, @Param("requestid") Long request_id);

    @Update("UPDATE specimen_application_link\n" +
            "SET status = 0 WHERE application_link_id = #{applicationLinkId}")
    Long removeSpecimen(@Param("applicationLinkId") Long applicationLinkId);

    @Select("SELECT sal.project_id FROM sys_project_document syd, specimen_application_link sal\n" +
            "WHERE syd.project_document_id = sal.project_id AND sal.application_id = #{applicationId} AND sal.status = 1")
    List<Long> getDocumentIdInSpecimen(@Param("applicationId") Long applicationId);

    @Select("SELECT project_document_id FROM sys_project_document_link WHERE project_package_id = #{projectPackageId}")
    List<Long> getDocumentListByPackageId(@Param("projectPackageId") Long projectPackageId);

    @Select("SELECT COUNT(*)\n" +
            "FROM specimen_application_link sal\n" +
            "LEFT JOIN specimen_application sa ON sa.application_id = sal.application_id\n" +
            "LEFT JOIN sys_project_document spd ON spd.project_document_id = sal.project_id\n" +
            "LEFT JOIN basic_info bi ON bi.object_id = sa.patient_office_id\n" +
            "WHERE sa.status = 1 AND spd.status = 1 AND sal.status = 1\n" +
            "AND bi.office_id = #{officeId}\n" +
            "AND is_register = 0\n" +
            "AND (placeholder_code LIKE CONCAT('%', #{search}, '%')\n" +
            "OR patient_name LIKE CONCAT('%', #{search}, '%')\n" +
            "OR project_document_name LIKE CONCAT('%', #{search}, '%'))\n")
    Long count4SpecimenUnregister(@Param("search") String search, @Param("officeId") Long officeId);

//    @Select("SELECT *, application_link_id AS specimenExtraId\n" +
//            "FROM specimen_application_link sal\n" +
//            "LEFT JOIN specimen_application sa ON sa.application_id = sal.application_id\n" +
//            "LEFT JOIN sys_project_document spd ON spd.project_document_id = sal.project_id\n" +
//            "LEFT JOIN basic_info bi ON bi.object_id = sa.patient_office_id\n" +
//            "WHERE sa.status = 1 AND spd.status = 1 AND sal.status = 1\n" +
//            "AND bi.office_id = #{officeId}\n" +
//            "AND is_register = 0\n" +
//            "AND (placeholder_code LIKE CONCAT('%', #{search}, '%')\n" +
//            "OR patient_name LIKE CONCAT('%', #{search}, '%')\n" +
//            "OR project_document_name LIKE CONCAT('%', #{search}, '%'))\n" +
//            "LIMIT #{current}, #{pageSize}")
//    List<SpecimenRegisterDTO> getSpecimenUnregister(@Param("pageSize") Integer pageSize, @Param("current") Integer current,
//                                                    @Param("search") String search, @Param("officeId") Long officeId);

//    @Insert("INSERT INTO specimen_register(specimen_extra_id, placeholder_code, patient_tag, patient_type, hospital_id,\n" +
//            "patient_name, patient_sex, patient_tel, door_num, patient_age, patient_age_type, department_id, bed_num,\n" +
//            "specimen_type, specimen_trait, diagnosis, doctor_id, sampling_time, report_time, docimaster_id,\n" +
//            "confirm_man_id, price, print_num, check_tip, remarks, tip_info, application_id, project_document_id,\n" +
//            "specimen_man_id, execute_department_id, specimen_window, tube_type, patient_id, patient_id_card," +
//            "daily_code ,institution,addr)\n" +
//            "VALUES (#{dto.specimenExtraId}, #{dto.placeholderCode}, #{dto.patientTag}, #{dto.patientType},\n" +
//            "#{dto.patientOfficeId}, #{dto.patientName}, #{dto.patientSex} ,#{dto.patientTel}, #{dto.doorNum},\n" +
//            "#{dto.patientAge}, #{dto.patientAgeType}, #{dto.departmentId}, #{dto.bedNum}, #{dto.specimenType},\n" +
//            "#{dto.specimenTrait}, #{dto.diagnosis}, #{dto.doctorId}, #{dto.samplingTime}, #{dto.reportTime},\n" +
//            "#{dto.docimasterId}, #{dto.confirmManId}, #{dto.price}, #{dto.printNum}, #{dto.checkTip}, #{dto.remarks},\n" +
//            "#{dto.tipInfo}, #{dto.applicationId}, #{dto.projectDocumentId}, #{dto.specimenManId}, #{dto.executeDepartmentId},\n" +
//            "#{dto.specimenWindow}, #{dto.tubeType}, #{dto.patientId}, #{dto.patientIdCard},#{dto.dailyCode},#{dto.institution},#{dto.addr})")
//    Boolean registerSpecimen(@Param("dto") SpecimenRegisterDTO specimenRegisterDTO);


    @Update("UPDATE specimen_application_link SET is_register = 1 WHERE application_link_id = #{applicationLinkId}")
    Boolean setRegister(@Param("applicationLinkId") Long applicationLinkId);

    @Select("<script>" +
            "SELECT COUNT(*) FROM specimen_register sr\n" +
            "JOIN specimen_application_link sa ON sr.specimen_extra_id = sa.application_link_id\n" +
            "JOIN sys_project_document spd ON sr.project_document_id = spd.project_document_id\n" +
            "JOIN basic_info bi ON bi.object_id = sr.hospital_id\n" +
            "WHERE sr.status = 1 AND sa.status = 1 AND spd.status = 1 AND sa.is_register = 1\n" +
            "<if test='startDateSearchSql!=null'> AND receive_time &gt;= #{startDateSearchSql}\n</if>" +
            "<if test='endDateSearchSql!=null'> AND receive_time &lt;= #{endDateSearchSql}\n</if>" +
            "<if test='officeId!=null'> AND bi.office_id= #{officeId}\n</if>" +
            "<if test=\"deviceGroupId != null\"> AND device_group_id = #{deviceGroupId} </if>\n" +
            "<if test=\"codeSearch != null\"> AND sr.placeholder_code LIKE CONCAT('%', #{codeSearch}, '%') </if>\n" +
            "<if test=\"specimenIdSearch != null\"> AND specimen_extra_id  LIKE CONCAT('%', #{specimenIdSearch}, '%') </if>\n" +
            "<if test=\"statusSearch != null\"> AND specimen_status  LIKE CONCAT('%', #{statusSearch}, '%') </if>\n" +
            "<if test=\"nameSearch != null\"> AND patient_name  LIKE CONCAT('%', #{nameSearch}, '%') </if>\n" +
            "<if test=\"hospitalId != null\"> AND hospital_id = #{hospitalId} </if>\n" +
            "<if test=\"sendLogo != null\"> AND (SELECT send_name FORM basic_send_logo sl WHERE sl.send_logo = sr.send_logo) = #{sendLogo} </if>\n" +
            // 删选单据类别
            "<if test=\"projectDocumentId != null\"> AND sr.project_document_id = #{projectDocumentId} </if>"+
            // 添加每日编码的筛选
            "<if test=\"dailyCode != null and dailyCode != '' \"> AND sr.daily_code = #{dailyCode} </if>"+
            "</script>")
    Long count4SpecimenRegister(@Param("deviceGroupId") Long deviceGroupId, @Param("startDateSearchSql") Date startDateSearchSql,
                                @Param("endDateSearchSql") Date endDateSearchSql, @Param("codeSearch") String codeSearch,
                                @Param("specimenIdSearch") Long specimenIdSearch, @Param("statusSearch") Long statusSearch,
                                @Param("officeId") Long officeId, @Param("nameSearch") String name, @Param("hospitalId") Long hospitalId,
                                @Param("sendLogo") Long sendLogo, @Param("projectDocumentId") Long projectDocumentId,
                                String dailyCode);

    /**
     *获取申请单总数
     * @param deviceGroupId
     * @param startDateSearchSql
     * @param endDateSearchSql
     * @param codeSearch
     * @param specimenIdSearch
     * @param officeId
     * @param name
     * @return
     */
    @Select("<script>" +
            "SELECT COUNT(*) " +
            "FROM specimen_request sr JOIN basic_info bi ON bi.object_id = sr.hospital_id\n" +
            "JOIN specimen_application_link sal ON sal.request_id = sr.request_id\n" +
            "JOIN sys_project_document spd ON sal.project_id = spd.project_document_id\n" +
            "LEFT JOIN specimen_request_link srl ON srl.request_id = sr.request_id\n" +
            "WHERE sr.status = 1 AND sal.status = 1 AND sal.is_register = 0\n" +
            "AND bi.office_id = #{officeId} \n" +
            "<if test='startDateSearchSql!=null'> AND sr.sampling_time &gt;= #{startDateSearchSql}\n</if>" +
            "<if test='endDateSearchSql!=null'> AND sr.sampling_time &lt;= #{endDateSearchSql}\n</if>" +
            "<if test=\"deviceGroupId != null\"> AND sr.device_group_id = #{deviceGroupId} </if>\n" +
            "<if test=\"codeSearch != null\"> AND sr.placeholder_code LIKE CONCAT('%', #{codeSearch}, '%') </if>\n" +
            "<if test=\"specimenIdSearch != null\"> AND sr.specimen_extra_id  LIKE CONCAT('%', #{specimenIdSearch}, '%') </if>\n" +
            "<if test=\"nameSearch != null\"> AND sr.patient_name  LIKE CONCAT('%', #{nameSearch}, '%') </if>\n" +
            "<if test=\"hospitalId != null\"> AND sr.hospital_id = #{hospitalId} </if>\n" +
            "</script>")
    Long count4Request(@Param("deviceGroupId") Long deviceGroupId, @Param("startDateSearchSql") Date startDateSearchSql,
                       @Param("endDateSearchSql") Date endDateSearchSql, @Param("codeSearch") String codeSearch,
                       @Param("specimenIdSearch") Long specimenIdSearch,
                       @Param("officeId") Long officeId, @Param("nameSearch") String name, @Param("hospitalId") Long hospitalId
    );

//    /**
//     *获取申请单列表
//     * @param deviceGroupId
//     * @param startDateSearchSql
//     * @param endDateSearchSql
//     * @param codeSearch
//     * @param specimenIdSearch
//     * @param officeId
//     * @param name
//     * @return
//     */
//    @Select("<script>" +
//            "SELECT *, application_link_id AS specimenExtraId, sr.hospital_id AS patientOfficeId, outpatient_id AS doorNum, application_department_id AS departmentId, application_doctor_id AS doctorId, check_man_id AS confirmManId,\n" +
//            "check_time AS receiveTime, sal.project_id AS projectDocumentId, spd.project_document_name AS projectDocumentName \n" +
//            "FROM specimen_request sr JOIN basic_info bi ON bi.object_id = sr.hospital_id\n" +
//            "JOIN specimen_application_link sal ON sal.request_id = sr.request_id\n" +
//            "JOIN sys_project_document spd ON sal.project_id = spd.project_document_id\n" +
//            "LEFT JOIN specimen_request_link srl ON srl.request_id = sr.request_id\n" +
//            "WHERE sr.status = 1 AND sal.status = 1 AND sal.is_register = 0\n" +
//            "AND bi.office_id = #{officeId} \n" +
//            "<if test='startDateSearchSql!=null'> AND sr.sampling_time &gt;= #{startDateSearchSql}\n</if>" +
//            "<if test='endDateSearchSql!=null'> AND sr.sampling_time &lt;= #{endDateSearchSql}\n</if>" +
//            "<if test=\"deviceGroupId != null\"> AND sr.device_group_id = #{deviceGroupId} </if>\n" +
//            "<if test=\"codeSearch != null\"> AND sr.placeholder_code LIKE CONCAT('%', #{codeSearch}, '%') </if>\n" +
//            "<if test=\"specimenIdSearch != null\"> AND sr.specimen_extra_id  LIKE CONCAT('%', #{specimenIdSearch}, '%') </if>\n" +
//            "<if test=\"nameSearch != null\"> AND sr.patient_name  LIKE CONCAT('%', #{nameSearch}, '%') </if>\n" +
//            "<if test=\"hospitalId != null\"> AND sr.hospital_id = #{hospitalId} </if>\n" +
//            "order by specimenExtraId desc LIMIT #{current},#{pageSize}" +
//            "</script>")
//    List<SpecimenCheckDTO> getRequestList(@Param("deviceGroupId") Long deviceGroupId, @Param("startDateSearchSql") Date startDateSearchSql,
//                                          @Param("endDateSearchSql") Date endDateSearchSql, @Param("codeSearch") String codeSearch,
//                                          @Param("specimenIdSearch") Long specimenIdSearch,
//                                          @Param("officeId") Long officeId, @Param("nameSearch") String name, @Param("hospitalId") Long hospitalId,
//                                          @Param("current") Integer current, @Param("pageSize") Integer pageSize
//    );

//    /**
//     * 通过requestId查询申请信息表
//     * @param requestId
//     * @return
//     */
//    @Select("<script>" +
//            "SELECT *, application_link_id AS specimenExtraId, sr.hospital_id AS patientOfficeId, outpatient_id AS doorNum, application_department_id AS departmentId, application_doctor_id AS doctorId, check_man_id AS confirmManId,\n" +
//            "check_time AS receiveTime, sal.project_id AS projectDocumentId \n" +
//            "FROM specimen_request sr JOIN basic_info bi ON bi.object_id = sr.hospital_id\n" +
//            "JOIN specimen_application_link sal ON sal.request_id = sr.request_id\n" +
//            "JOIN sys_project_document spd ON sal.project_id = spd.project_document_id\n" +
//            "LEFT JOIN specimen_request_link srl ON srl.request_id = sr.request_id\n" +
//            "WHERE sr.status = 1 AND sal.status = 1 AND sal.is_register = 0\n" +
//            "AND sr.request_id = #{requestId} \n" +
//            "order by specimenExtraId desc " +
//            "</script>")
//    List<SpecimenRegisterDTO> getRequestListByRequestId(@Param("requestId") Long requestId);

//    /**
//     * 通过医院的和条码获取申请列表
//     * @param hospitalId
//     * @param codeSearch
//     * @return
//     */
//    @Select("<script>" +
//            "SELECT *, application_link_id AS specimenExtraId, sr.hospital_id AS patientOfficeId, outpatient_id AS doorNum, application_department_id AS departmentId, application_doctor_id AS doctorId, check_man_id AS confirmManId,\n" +
//            "check_time AS receiveTime, sal.project_id AS projectDocumentId \n" +
//            "FROM specimen_request sr JOIN basic_info bi ON bi.object_id = sr.hospital_id\n" +
//            "JOIN specimen_application_link sal ON sal.request_id = sr.request_id\n" +
//            "JOIN sys_project_document spd ON sal.project_id = spd.project_document_id\n" +
//            "LEFT JOIN specimen_request_link srl ON srl.request_id = sr.request_id\n" +
//            "WHERE sr.status = 1 AND sal.status = 1 AND sal.is_register = 0\n" +
//            "AND sr.hospital_id = #{hospitalId} \n" +
//            "AND sr.placeholder_code = #{placecode} \n" +
//            "order by specimenExtraId desc " +
//            "</script>")
//    List<SpecimenRegisterDTO> getRequestListByPlacecode(@Param("hospitalId") Long hospitalId, @Param("placecode") String codeSearch);


    @Select("SELECT COUNT(*) " +
            "FROM specimen_register sr "+
            "WHERE sr.status = 1 \n" +
            "AND sr.hospital_id = #{hospitalId} " +
            "AND sr.project_document_id = #{projectdoc} " +
            "AND sr.placeholder_code=#{placecode} \n")
    Long checkHasPlaceCode(@Param("hospitalId") Long hospitalId, @Param("placecode") String codeSearch, @Param("projectdoc") Long projectdoc);

//    @Select("<script>" +
//            "SELECT sr.*," +
//            "(select doctor_name  from hospital_doctor  where doctor_id = sr.doctor_id) doctorName," +
//            "(select type_info_name from basic_type_info where sr.department_id = type_info_id and type_info_type=3) departmentName," +
//            "(select project_document_name from sys_project_document where project_document_id = sr.project_document_id) projectDocumentName "+
//            " FROM specimen_register sr\n" +
//            "JOIN specimen_application_link sa ON sr.specimen_extra_id = sa.application_link_id\n" +
//            "JOIN sys_project_document spd ON sr.project_document_id = spd.project_document_id\n" +
//            "JOIN basic_info bi ON bi.object_id = sr.hospital_id\n" +
//            "WHERE sr.status = 1 AND sa.status = 1 AND spd.status = 1 AND sa.is_register = 1\n" +
//            "<if test='startDateSearchSql!=null'> AND receive_time &gt;= #{startDateSearchSql}\n</if>" +
//            "<if test='officeId!=null'> A" +
//            "ND bi.office_id= #{officeId}\n</if>" +
//            "<if test='endDateSearchSql!=null'> AND receive_time &lt;= #{endDateSearchSql}\n</if>" +
//            "<if test=\"deviceGroupId != null\"> AND device_group_id = #{deviceGroupId} </if>\n" +
//            "<if test=\"codeSearch != null\"> AND sr.placeholder_code LIKE CONCAT('%', #{codeSearch}, '%') </if>\n" +
//            "<if test=\"specimenIdSearch != null\"> AND specimen_extra_id  LIKE CONCAT('%', #{specimenIdSearch}, '%') </if>\n" +
//            "<if test=\"statusSearch != null\"> AND specimen_status  LIKE CONCAT('%', #{statusSearch}, '%') </if>\n" +
//            "<if test=\"nameSearch != null\"> AND patient_name  LIKE CONCAT('%', #{nameSearch}, '%') </if>\n" +
//            "<if test=\"hospitalid != null\"> AND hospital_id = #{hospitalid} </if>\n" +
//            "<if test=\"sendLogo != null\"> AND sendLogo = #{sendLogo} </if>\n" +
//            // 删选单据类别
//            "<if test=\"projectDocumentId != null\"> AND sr.project_document_id = #{projectDocumentId} </if>"+
//            // 添加每日编码的筛选
//            "<if test=\"dailyCode != null and dailyCode != '' \" > AND sr.daily_code = #{dailyCode} </if>"+
//            "ORDER BY specimen_register_id desc " +
//            "LIMIT #{current}, #{pageSize}" +
//            "</script>")
//    List<SpecimenRegisterDTO> getSpecimenRegister(@Param("current") Integer current, @Param("pageSize") Integer pageSize,
//                                                  @Param("deviceGroupId") Long deviceGroupId, @Param("startDateSearchSql") Date startDateSearchSql,
//                                                  @Param("endDateSearchSql") Date endDateSearchSql, @Param("codeSearch") String codeSearch,
//                                                  @Param("specimenIdSearch") Long specimenIdSearch, @Param("statusSearch") Long statusSearch,
//                                                  @Param("officeId") Long officeId, @Param("nameSearch") String name, @Param("hospitalid") Long hospitalId,
//                                                  @Param("sendLogo") Long sendLogo, @Param("projectDocumentId") Long projectDocumentId
//            , String dailyCode
//    );

    @Update("UPDATE specimen_register SET status = 0 WHERE specimen_register_id = #{specimenRegisterId}")
    Boolean registerSpecimenDelete(@Param("specimenRegisterId") Long specimenRegisterId);

    @Update("UPDATE specimen_application_link SET is_register = 0 WHERE application_link_id = #{applicationLinkId}")
    Boolean setUnregister(@Param("applicationLinkId") Long applicationLinkId);

//    @Update("UPDATE specimen_register SET placeholder_code = #{dto.placeholderCode}, patient_tag = #{dto.patientTag},\n" +
//            "patient_type = #{dto.patientType}, hospital_id = #{dto.hospitalId}, patient_name = #{dto.patientName},\n" +
//            "patient_sex = #{dto.patientSex}, patient_tel = #{dto.patientTel}, door_num = #{dto.doorNum},\n" +
//            "patient_age = #{dto.patientAge}, patient_age_type = #{dto.patientAgeType}, department_id = #{dto.departmentId},\n" +
//            "bed_num = #{dto.bedNum}, specimen_type = #{dto.specimenType}, specimen_trait = #{dto.specimenTrait},\n" +
//            "diagnosis = #{dto.diagnosis}, doctor_id = #{dto.doctorId}, sampling_time = #{dto.samplingTime},\n" +
//            "report_time = #{dto.reportTime}, receive_time = #{dto.receiveTime}, docimaster_id = #{dto.docimasterId},\n" +
//            "confirm_man_id = #{dto.confirmManId}, specialist_sign = #{dto.specialistSign}, \n" +
//            "price = #{dto.price}, print_num = #{dto.printNum}, check_tip = #{dto.checkTip}, remarks = #{dto.remarks},\n" +
//            "tip_info = #{dto.tipInfo}, application_id = #{dto.applicationId}, project_document_id = #{dto.projectDocumentId}\n" +
//            ", comment_fst = #{dto.commentFst}, " +
//            "comment_sec = #{dto.commentSec}, " +
//            "comment_thr = #{dto.commentThr}," +
//            "comment_for = #{dto.commentFor}," +
//            "patient_tel = #{dto.patientTel},institution= #{dto.institution} " +
//            "WHERE specimen_register_id = #{dto.specimenRegisterId}")
//    Boolean registerSpecimenEdit(@Param("dto") SpecimenRegisterDTO specimenRegisterDTO);

    @Update("UPDATE specimen_register SET specimen_status = #{specimenStatus} WHERE specimen_register_id = #{specimenRegisterId}")
    Boolean setSpecimenStatus(@Param("specimenRegisterId") Long specimenRegisterId, @Param("specimenStatus") Integer specimenStatus);

    @Update("UPDATE specimen_register SET specimen_status = #{specimenStatus}, " +
            "tip_info =null,comment_fst=null,comment_sec=null,comment_thr=null,comment_for=null " +
            "WHERE specimen_register_id = #{specimenRegisterId}")
    Boolean setSpecimenStatusClear(@Param("specimenRegisterId") Long specimenRegisterId, @Param("specimenStatus") Integer specimenStatus);

    @Update("UPDATE specimen_register SET specimen_status = #{specimenStatus} WHERE specimen_extra_id = #{specimenRegisterId}")
    Boolean setSpecimenStatusByExtraId(@Param("specimenRegisterId") Long specimenRegisterId, @Param("specimenStatus") Integer specimenStatus);

    @Select("SELECT COUNT(*) FROM specimen_register_result WHERE application_link_id = #{applicationLinkId} ")
    Long count4Result(@Param("applicationLinkId") Long applicationLinkId);

//    @Select("SELECT specimen_result_id, application_link_id, srr.project_id, project_value, sp.device_group_id, sp.project_name,sp.example_range," +
//            "sp.danger_min,sp.danger_max  \n" +
//            "FROM specimen_register_result srr, sys_project sp\n" +
//            "WHERE sp.project_id = srr.project_id AND sp.status = 1 AND application_link_id = #{applicationLinkId} " +
//            "order by srr.project_id \n" +
//            "LIMIT #{current}, #{pageSize}")
//    List<SpecimenResultDTO> getSpecimenResult(@Param("current") Integer current, @Param("pageSize") Integer pageSize,
//                                              @Param("applicationLinkId") Long applicationLinkId);

//    @Select("SELECT specimen_result_id, application_link_id, srr.project_id, project_value, sp.device_group_id, sp.project_name,sp.example_range," +
//            "sp.danger_min,sp.danger_max  \n" +
//            "FROM specimen_register_result srr, sys_project sp\n" +
//            "WHERE sp.project_id = srr.project_id AND sp.status = 1 AND application_link_id = #{applicationLinkId} " +
//            "order by srr.project_id ")
//    List<SpecimenResultDTO> getSpecimenAllResult(@Param("applicationLinkId") Long applicationLinkId);

    @Select("SELECT graph_link_id, application_link_id, graph_url AS url FROM specimen_graph_link WHERE application_link_id = #{applicationLinkId}")
    List<ImageDTO> getImage(@Param("applicationLinkId") Long applicationLinkId);

    @Select("SELECT DISTINCT specimen_extra_id FROM specimen_register")
    List<Long> getAllSpecimenId();

//    @Select("SELECT * FROM specimen_file")
//    List<WordDTO> getAllPdfTemplate();

//    @Select("SELECT *, application_link_id AS specimenExtraId, sr.hospital_id AS patientOfficeId, outpatient_id AS doorNum,\n" +
//            "application_department_id AS departmentId, application_doctor_id AS doctorId, check_man_id AS confirmManId,\n" +
//            "check_time AS receiveTime, sal.project_id AS projectDocumentId FROM specimen_request sr\n" +
//            "JOIN basic_info bi ON bi.object_id = sr.hospital_id\n" +
//            "JOIN specimen_application_link sal ON sal.request_id = sr.request_id\n" +
//            "JOIN sys_project_document spd ON sal.project_id = spd.project_document_id\n" +
//            "LEFT JOIN specimen_request_link srl ON srl.request_id = sr.request_id\n" +
//            "WHERE sr.status = 1 AND sal.status = 1 AND sal.is_register = 0\n" +
//            "AND bi.office_id = #{officeId} order by specimenExtraId desc")
//    List<SpecimenRegisterDTO> getAllSpecimenRequest(@Param("officeId") Long officeId);



//    @Select("SELECT *, application_link_id AS specimenExtraId, sr.hospital_id AS patientOfficeId, outpatient_id AS doorNum,\n" +
//            "application_department_id AS departmentId, application_doctor_id AS doctorId, check_man_id AS confirmManId,\n" +
//            "check_time AS receiveTime, sal.project_id AS projectDocumentId FROM specimen_request sr \n" +
//            "JOIN basic_info bi ON bi.object_id = sr.hospital_id \n" +
//            "JOIN specimen_application_link sal ON sal.request_id = sr.request_id \n" +
//            "JOIN sys_project_document spd ON sal.project_id = spd.project_document_id \n" +
//            "LEFT JOIN specimen_request_link srl ON srl.request_id = sr.request_id \n" +
//            "WHERE sr.status = 1 AND sal.status = 1 AND sal.is_register = 0 \n" +
//            "and sal.application_link_id=#{extid}")
//    SpecimenRegisterDTO getSpecimenRequestByApplicationId(@Param("extid") Long extid);

//    @Select("SELECT *, application_link_id AS specimenExtraId\n" +
//            "FROM specimen_application_link sal, specimen_application sa, sys_project_document spd ,basic_info bi\n" +
//            "WHERE sal.application_id = sa.application_id\n" +
//            "AND bi.object_id = sa.patient_office_id\n" +
//            "AND sal.project_id = spd.project_document_id\n" +
//            "AND bi.office_id = #{officeId}\n" +
//            "AND sa.status = 1 AND spd.status = 1 AND sal.status = 1\n" +
//            "AND is_register = 0")
//    List<SpecimenRegisterDTO> getAllUnregister(@Param("officeId") Long officeId);

    @Update("UPDATE specimen_register SET report_time = NOW(), confirm_man_id = #{confirmManId}, specimen_status = 3 WHERE specimen_register_id = #{specimenRegisterId}")
    Boolean checkSpecimen(@Param("specimenRegisterId") Long specimenRegisterId, @Param("confirmManId") Long confirmManId);

    @Update("UPDATE specimen_register_result SET project_value = #{projectValue} WHERE specimen_result_id = #{specimenResultId}")
    Boolean resultEdit(@Param("specimenResultId") Long specimenResultId, @Param("projectValue") String projectValue);

    @Delete("DELETE FROM specimen_register_result WHERE application_link_id =#{specimenExtraId}")
    Boolean clearResult(@Param("specimenExtraId") Long specimenExtraId);

    @Delete("DELETE FROM specimen_graph_link WHERE application_link_id =#{specimenExtraId}")
    Boolean clearImage(@Param("specimenExtraId") Long specimenExtraId);

    @Select("SELECT receive_time FROM specimen_register WHERE specimen_extra_id = #{applicationLinkId} AND status = 1")
    String getReceiveTime(@Param("applicationLinkId") Long applicationLinkId);

    @Select("SELECT daily_code FROM specimen_register sr JOIN basic_info bi ON bi.object_id = sr.hospital_id\n" +
            "WHERE bi.office_id = #{officeId}\n" +
            "AND receive_time >= #{startDateSearchSql}\n" +
            "AND receive_time <= #{endDateSearchSql}\n" +
            "AND sr.status = 1")
    List<String> getDailyCodeList(@Param("officeId") Long officeId, @Param("startDateSearchSql") Date startDateSearchSql,
                                  @Param("endDateSearchSql") Date endDateSearchSql);

    @Select("SELECT count(daily_code) FROM specimen_register sr JOIN basic_info bi ON bi.object_id = sr.hospital_id\n" +
            "WHERE bi.office_id = #{officeId}\n" +
            "AND receive_time >= #{startDateSearchSql}\n" +
            "AND receive_time <= #{endDateSearchSql}\n" +
            "AND sr.status = 1 AND sr.specimen_status = 3\n" +
            "AND daily_code = #{dailyCode}")
    Integer getDailyCodeListWithCompleted(@Param("officeId") Long officeId, @Param("startDateSearchSql") Date startDateSearchSql,
                                          @Param("endDateSearchSql") Date endDateSearchSql, @Param("dailyCode") String dailyCode);

    @Select("SELECT count(daily_code) FROM specimen_register sr JOIN basic_info bi ON bi.object_id = sr.hospital_id\n" +
            "WHERE bi.office_id = #{officeId}\n" +
            "AND receive_time >= #{startDateSearchSql}\n" +
            "AND receive_time <= #{endDateSearchSql}\n" +
            "AND sr.status = 1 AND daily_code = #{dailyCode}")
    Integer getDailyCodeCount(@Param("officeId") Long officeId, @Param("startDateSearchSql") Date startDateSearchSql,
                              @Param("endDateSearchSql") Date endDateSearchSql, @Param("dailyCode") String dailyCode);

    @Update("UPDATE specimen_register SET daily_code = #{dailyCode} WHERE specimen_extra_id = #{applicationLinkId}\n" +
            "AND status = 1")
    Long setDailyCode(@Param("applicationLinkId") Long applicationLinkId, @Param("dailyCode") String dailyCode);

    @Select("SELECT specimen_extra_id FROM specimen_register sr JOIN basic_info bi ON bi.object_id = sr.hospital_id\n" +
            "WHERE bi.office_id = #{officeId}\n" +
            "AND receive_time >= #{startDateSearchSql}\n" +
            "AND receive_time <= #{endDateSearchSql}\n" +
            "AND daily_code = #{dailyCode} AND sr.status = 1")
    List<Long> getAppLinkIdByDailyCode(@Param("officeId") Long officeId, @Param("startDateSearchSql") Date startDateSearchSql,
                                       @Param("endDateSearchSql") Date endDateSearchSql, @Param("dailyCode") String dailyCode);

    @Update("UPDATE specimen_application_link SET status = 0 WHERE request_id = #{requestId} and is_register=0")
    Long removeRequest(@Param("requestId") Long requestId);

    @Update("UPDATE specimen_request SET status = 0 WHERE request_id = #{requestId}")
    Long removeRequestId(@Param("requestId") Long requestId);

    @Select("select count(*) from specimen_register sr,specimen_application_link sal " +
            "where sr.specimen_extra_id = sal.application_link_id " +
            "and sr.status = 1 " +
            "and sal.status = 1 " +
            "and sr.specimen_extra_id = #{samplid}")
    long checkHasSampleID(@Param("samplid") Long applicationLinkId);

//    @Select("select specimen_register_id,project_document_id,tip_info," +
//            "comment_fst,comment_sec,comment_thr,comment_for,specimen_status " +
//            "from specimen_register sr " +
//            "where sr.status = 1 " +
//            "and sr.specimen_extra_id = #{key}")
//    SpecimenRegisterDTO getSpecimenRegisterByApplicationId(@Param("key") Long key);


    @Update("UPDATE specimen_register SET tip_info = #{tinfo},comment_fst=#{comfst}," +
            "comment_sec=#{comsec},comment_thr=#{comthr},comment_for=#{comfor} " +
            "WHERE specimen_register_id = #{srid}")
    Boolean updateTips(@Param("srid") Long srid, @Param("tinfo") String tinfo,
                       @Param("comfst") String comfst, @Param("comsec") String comsec,
                       @Param("comthr") String comthr, @Param("comfor") String comfor);
}
